package gvo.roStart;

import gvo.basicDatatypes.AutorenDat;
import gvo.basicDatatypes.BuchDat;
import gvo.basicDatatypes.GVOall;
import gvo.basicDatatypes.StichwortDat;
import gvo.newSearch.GlazedGVOall;
import gvo.presets.Presets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.swing.SwingUtilities;

import ca.odell.glazedlists.EventList;

public class ROStart {
	private Presets pre = new Presets();
	private Connection conn;
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		 SwingUtilities.invokeLater(new Runnable() {
		      public void run() {
		        new ROStart();
		      }
		    });

	}
	public ROStart(){
		startDB();
	}
	public boolean startDB(){
		boolean res = true;
		try {
			Class.forName("org.hsqldb.jdbcDriver");
			String verzeichnis;
			if (pre.isDbPathRelativ()) {
				verzeichnis = System.getProperty("user.dir");
			} else {
				verzeichnis = "";
			}
			final StringBuffer complVerz = new StringBuffer(verzeichnis);
			complVerz.append(pre.getDbPath());
			complVerz.append("biblio");
			conn = DriverManager.getConnection("jdbc:hsqldb:" + complVerz,
					pre.getDb_User(), pre.getDb_Pw());
			conn.setAutoCommit(true);
			
		} catch (SQLException ex1) {
			ex1.printStackTrace();
			res = false;
		} catch (ClassNotFoundException ex2) {
			ex2.printStackTrace();
			res = false;
		}
		return res;
	}
	public void loadGVOall(EventList<GlazedGVOall> glazedGVOalls) {
		Statement stmtJ = null;
		ResultSet rs = null;
		String query = "SELECT * FROM Buecher";
		try {
			stmtJ = conn.createStatement();
			rs = stmtJ.executeQuery(query);
			GVOall res;
			while(rs.next()) {
				res= new GVOall();
				getBuchDatFromRS(rs, res.buch);
				getAutorenForBuch(res.buch.getIdBuch(), res.autoren);
				getStichworteForBuch(res.buch.getIdBuch(), res.stichworte);
				glazedGVOalls.add(new GlazedGVOall(res));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		closeResultSet(rs);
		closeStatement(stmtJ);
		
	}
	private static void getBuchDatFromRS(ResultSet rs, BuchDat bu) throws SQLException{
		bu.setIdBuch(rs.getInt("idBuch"));
		bu.setTitel(rs.getString("titel"));
		bu.setReroNr(rs.getString("reroNr"));
		bu.setReihe(rs.getString("reihe"));
		bu.setOrtJahr(rs.getString("ortJahr"));
		bu.setIsbnPreis(rs.getString("isbnPreis"));
		bu.setAnmerkung(rs.getString("anmerkung"));
		bu.setKollation(rs.getString("kollation"));
		bu.setAusgabeVersion(rs.getString("ausgabeVersion"));
		bu.setSignatur(rs.getString("signatur"));
		bu.setEnthaltenIn(rs.getString("enthaltenIn"));
		bu.setNeueSignatur(rs.getBoolean("neueSignatur"));
		bu.setInBiblio(rs.getBoolean("inBiblio"));
		bu.setMarkiert(rs.getBoolean("markiert"));
		bu.setNotiz(rs.getString("notiz"));
		bu.isInDB = true;
		bu.isVerified = true;	
	}
	public void getAutorenForBuch(int idBuch, ArrayList <AutorenDat> autoren){
		
		Statement stmtJ = null;
		ResultSet rs = null;
		String query = "SELECT * FROM V_AU Where idBuch="+idBuch;
		try {
			stmtJ = conn.createStatement();
			rs = stmtJ.executeQuery(query);
			autoren.clear();
			while (rs.next()) {
				autoren.add(new AutorenDat(rs.getInt("idAutor"),rs.getString("name"),rs.getString("nachname"), rs.getString("vorname"), rs.getString("zusatz"), rs.getString("notiz"), true, true));
			}
			
		} catch (SQLException e) {
			showMessage("SQLException "+e.getMessage(), Level.WARNING, ROStart.class.getName());
		}
		closeResultSet(rs);
		closeStatement(stmtJ);
	}
	public void getStichworteForBuch(int idBuch, ArrayList <StichwortDat> stichworte){
		
		String query = "SELECT * FROM V_ST Where idBuch="+idBuch;
		Statement stmtJ = null;
		ResultSet rs = null;
		try {
			stmtJ = conn.createStatement();
			rs = stmtJ.executeQuery(query);
			stichworte.clear();
			while (rs.next()) {
				stichworte.add(new StichwortDat( rs.getInt("idStichwort"), rs.getString("Stichwort"), true, true));
			}
		} catch (SQLException e) {
			showMessage("SQLException "+e.getMessage(), Level.WARNING, ROStart.class.getName());
		}
		closeResultSet(rs);
		closeStatement(stmtJ);
	}
	public static void closeStatement(Statement stmtJ){
		if(stmtJ!=null){
			try {
				stmtJ.close();
			} catch (SQLException e) {
				showMessage("Statement konnte nicht geschlossen werden", Level.WARNING, ROStart.class.getName());
				e.printStackTrace();
			}
		}
		
	}
	public static void closeResultSet(ResultSet rs){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				showMessage("ResultSet konnte nicht geschlossen werden", Level.WARNING, ROStart.class.getName());
				e.printStackTrace();
			}
		}
		
	}
	/**
	 * @param msg
	 *  the message
	 * @param level (use from Config - Severe!)
	 *   SEVERE (highest value), 
	 *   WARNING, 
	 *   INFO, 
	 *   CONFIG, 
	 *   FINE, 
	 *   FINER, 
	 *   FINEST (lowest value), 
	 *   In addition there is a level OFF that can be used to turn off logging, and a level ALL that can be used to enable logging of all messages.
	 * @param classname
	 * the name of the class, where the msg comes from
	 */
	public static void showMessage(String msg, Level level,  String classname){
		Logger log = Logger.getLogger(classname);
		log.log(level, msg);
	}
}
